package com.hnkywl.sts.dao.cggl;

import com.hnkywl.sts.dao.base.RegionDao;
import com.hnkywl.sts.dao.sys.YgDao;
import com.hnkywl.sts.entity.cggl.Cgd;
import com.hnkywl.sts.entity.cggl.Cgdmx;
import com.hnkywl.sts.entity.cggl.Glfy;
import com.hnkywl.sts.entity.htgl.Cght;
import com.hnkywl.sts.entity.organ.Ck;
import com.hnkywl.sts.entity.organ.Nbgs;
import com.hnkywl.sts.entity.sfgl.Sfk;
import com.hnkywl.sts.entity.xsgl.Kh;
import com.hnkywl.sts.entity.zjgl.Jzjs;
import com.hnkywl.sts.service.sys.YgService;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.*;
import org.hibernate.transform.Transformers;
import org.hibernate.type.Type;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.sql.*;
import java.text.ParseException;
import java.util.*;
import java.util.Date;

/**
 * Created with IntelliJ IDEA.
 * User: Administrator
 * Date: 13-4-23
 * Time: 上午11:15
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class CgdDao extends SimpleHibernateTemplate<Cgd, Long> {
    private CgdmxDao cgdmxDao;

    private YgDao ygDao;


    public Page<Cgd> findAllCgdList(Integer sfgq, String wzbm, String cgdh, String htbm, Long dzckId, Date fhrqStart, Date fhrqEnd, String cglx, Long cggsId, Long cgbmId, Long cgrId, String zt, String sftzd, String htType, Long gysid, String rkzt, String zdr, Boolean dlkhcgd, int pageNo, int pageSize) throws ParseException {
        Page<Cgd> page = new Page<Cgd>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("id");

        DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Cgdmx.class);

        detachedCriteria.createAlias("gg", "gg", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("cgd.id").as("cgd.id")))
                .add(Restrictions.or(Restrictions.like("gg.code", wzbm, MatchMode.START), Restrictions.like("gg.oldcode", wzbm, MatchMode.START)));

        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("gys", "gys", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbgs", "sbgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbbm", "sbbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbr", "sbr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cggs", "cggs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgbm", "cgbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cght","cght",CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgr", "cgr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("dzck", "dzck", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("cgsl").as("cgsl"))
                                .add(Property.forName("cgje").as("cgje"))
                                .add(Property.forName("cgbhsje").as("cgbhsje"))
                                .add(Property.forName("glfyje").as("glfyje"))
                                .add(Projections.sqlProjection("this_.date_created as czjr", new String[]{"czjr"}, new Type[]{Hibernate.DATE}))
                                .add(Property.forName("id").as("id"))
                                .add(Property.forName("ysh").as("ysh"))
                                .add(Property.forName("gys.id").as("gys_id"))
                                .add(Property.forName("gys.qc").as("gys_qc"))
                                .add(Property.forName("gys.jc").as("gys_jc"))
                                .add(Property.forName("cght.htbm").as("htbm"))
                                .add(Projections.sqlProjection("this_.cgdh as cgdh", new String[]{"cgdh"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlProjection("this_.cglx as cglx", new String[]{"cglx"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlProjection("this_.fhrq as fhrq", new String[]{"fhrq"}, new Type[]{Hibernate.DATE}))
                                .add(Projections.sqlProjection("this_.jhdhrq as jhdhrq", new String[]{"jhdhrq"}, new Type[]{Hibernate.DATE}))
                                .add(Projections.sqlProjection("this_.cgsj as cgsj", new String[]{"cgsj"}, new Type[]{Hibernate.DATE}))
                                .add(Projections.sqlProjection("this_.yrk as yrk", new String[]{"yrk"}, new Type[]{Hibernate.STRING}))
                                .add(Property.forName("sbgs.id").as("sbgs_id"))
                                .add(Property.forName("sbgs.jc").as("sbgs_jc"))
                                .add(Property.forName("sbbm.id").as("sbbm_id"))
                                .add(Property.forName("sbbm.qc").as("sbbm_qc"))
                                .add(Property.forName("sbr.id").as("sbr_id"))
                                .add(Property.forName("sbr.xm").as("sbr_xm"))
                                .add(Property.forName("cggs.id").as("cggs_id"))
                                .add(Property.forName("cggs.qc").as("cggs_qc"))
                                .add(Property.forName("cggs.jc").as("cggs_jc"))
                                .add(Property.forName("cgbm.id").as("cgbm_id"))
                                .add(Property.forName("cgbm.qc").as("cgbm_qc"))
                                .add(Property.forName("cgr.id").as("cgr_id"))
                                .add(Property.forName("cgr.xm").as("cgr_xm"))
                                .add(Property.forName("dzck.id").as("dzck_id"))
                                .add(Property.forName("dzck.mc").as("dzck_mc"))
                                .add(Property.forName("createUser.xm").as("createUser_xm"))
                                .add(Projections.sqlProjection("this_.sftzd as sftzd", new String[]{"sftzd"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlProjection("this_.zt as zt", new String[]{"zt"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlProjection("this_.date_created as cjsj", new String[]{"cjsj"}, new Type[]{Hibernate.DATE}))
                )
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (StringUtils.isNotEmpty(wzbm)) {
            criteria.add(Property.forName("id").in(detachedCriteria));
        }
        if (StringUtils.isNotEmpty(cgdh)) {
            criteria.add(Property.forName("cgdh").like(cgdh, MatchMode.ANYWHERE));
        }
        if (StringUtils.isNotEmpty(htbm)) {
            criteria.add(Property.forName("cght.htbm").like(htbm, MatchMode.ANYWHERE));
        }
        if (StringUtils.isNotEmpty(zdr)) {
            criteria.add(Property.forName("createUser.xm").like(zdr, MatchMode.ANYWHERE));
        }
        if (fhrqStart != null) {
            criteria.add(Restrictions.ge("dateCreated", sDaysAfterNowDate(fhrqStart)));
        } else {
            criteria.add(Restrictions.ge("dateCreated",sDaysAfterNowDate(7)));
        }
        if (fhrqEnd != null) {
            criteria.add(Restrictions.le("dateCreated", jDaysAfterNowDate(fhrqEnd)));
        } else {
            Date end = jDaysAfterNowDate(new Date());
            criteria.add(Restrictions.le("dateCreated", end));
        }
        if (sfgq != null && sfgq != 0) {
            Date end = DaysAfterNowDate(new Date());
            if (sfgq == 1) {
                criteria.add(Restrictions.ge("jhdhrq", end));
            } else {
                criteria.add(Restrictions.lt("jhdhrq", end));
            }
        }
        if (StringUtils.isNotEmpty(zt)) {
            if (zt != null && !zt.isEmpty() && "ybc".equals(zt)) {
                criteria.add(Restrictions.eq("zt", zt));
            } else if (zt != null && !zt.isEmpty() && "ytj".equals(zt)) {
                criteria.add(Restrictions.eq("zt", zt));
            } else if (zt != null && !zt.isEmpty() && "ysp".equals(zt)) {
                criteria.add(Restrictions.eq("zt", zt));
            }
        }

        if (StringUtils.isNotEmpty(htType) && htType.equals("bght")) {
            criteria.add(Restrictions.eq("cglx", htType));
        } else {
            criteria.add(Restrictions.ne("cglx", "bght"));
        }

        if (StringUtils.isNotEmpty(cglx)) {
            criteria.add(Restrictions.eq("cglx", cglx));
        }
        if (dzckId != null) {
            criteria.add(Restrictions.eq("dzck.id", dzckId));
        }
        if (cggsId != null) {
            criteria.add(Restrictions.eq("sbgs.id", cggsId));
        }
        if (cgbmId != null) {
            criteria.add(Restrictions.eq("sbbm.id", cgbmId));
        }
        if (cgrId != null) {
            criteria.add(Restrictions.eq("sbr.id", cgrId));
        }
        if (gysid != null) {
            criteria.add(Restrictions.eq("gys.id", gysid));
        }
        if (StringUtils.isNotEmpty(rkzt)) {
            criteria.add(Restrictions.eq("yrk", rkzt.equals("true")));
        }

        if (sftzd != null) {
            criteria.add(Restrictions.or(Restrictions.isNull("sftzd"), Restrictions.eq("sftzd", false)));
        } else {
            criteria.add(Restrictions.eq("zt", "ysp"));
            criteria.add(Restrictions.or(Restrictions.isNull("yjs"), Restrictions.eq("yjs", false)));
        }

        if (dlkhcgd != null && dlkhcgd) {
            DetachedCriteria subDetachedCriteria = DetachedCriteria.forClass(Nbgs.class);
            subDetachedCriteria
                    .setProjection(Projections.projectionList()
                            .add(Property.forName("id").as("id")));
            criteria.add(Property.forName("sbgs.id").in(subDetachedCriteria));
        }

        createCgdDataRegion(criteria);
        criteria.add(Restrictions.ne("cglx", "nbcg"));
        Page list = this.findByCriteria(page, criteria);
        return list;
    }


    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    private Date sDaysAfterNowDate(int n) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.add(Calendar.DAY_OF_MONTH, -n);
        return rightNow.getTime();
    }

    public String findAllCgd() {
        Criteria countCriteria = getSession().createCriteria(this.entityClass)
                .setProjection(Projections.rowCount());
        Date start = DaysAfterNowDate(new Date());
        countCriteria.add(Restrictions.ge("dateCreated", start));
        Date end = jDaysAfterNowDate(new Date());
        countCriteria.add(Restrictions.le("dateCreated", end));
        countCriteria.add(Restrictions.or(Restrictions.isNull("sftzd"), Restrictions.eq("sftzd", false)));
        countCriteria.add(Restrictions.eq("zt", "ybc"));
        countCriteria.add(Restrictions.ne("cglx", "bght"));
        countCriteria.add(Restrictions.ne("cglx", "nbcg"));
        createCgdDataRegion(countCriteria);
        Long totalCount = (Long) countCriteria.uniqueResult();
        return totalCount.toString();
    }

    public Page<Map> findAllBgCgdList(Integer sfgq, String wzbm, String cgdh, Long dzckId, Date fhrqStart, Date fhrqEnd, String cglx, Long cggsId, Long cgbmId, Long cgrId, String zt, String sftzd, String htType, Long gysid, String rkzt, String zdr, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();

        String regionSql = StringUtils.EMPTY;
        if (existsSQLQuery("select 1 from t_sys_yg_bmfw where yg_id = ?", YgService.getCurrentYg().getId())) {
            regionSql += "  inner join v_wzbm_gg_yg_search bmfw  on mx.GG_ID = bmfw.ggid and bmfw.yg_id=" + YgService.getCurrentYg().getId();
        } else if (existsSQLQuery("select 1 from v_wzbm_gg_search  where gsid = ?", YgService.getCurrentYgGsId())) {
            regionSql += "  inner join v_wzbm_gg_search bmfw on mx.GG_ID = bmfw.ggid and bmfw.gsid=" + YgService.getCurrentYgGsId();
        }


        String sqlCountBase = "select count(distinct this_.id)  from T_BIZ_CGD this_ left outer join T_SYS_YG createuser9_ on this_.CREATEUSER_ID = createuser9_.id " +
                " left outer join T_BIZ_CGDMX mx on mx.cgd_id=this_.id and mx.deleted!=1 " + regionSql +
                "  left outer join T_SYS_GSJG cgbm6_ on this_.CGBM_ID = cgbm6_.id left outer join T_SYS_GSJG cggs5_ on this_.CGGS_ID = cggs5_.id " +
                "  left outer join T_SYS_YG cgr7_ on this_.CGR_ID = cgr7_.id left outer join T_SYS_CK dzck8_ on this_.DZCK_ID = dzck8_.id " +
                "  left outer join T_SYS_GSJG gys1_ on this_.GYS_ID = gys1_.id left outer join T_SYS_GSJG sbbm3_ on this_.SBBM_ID = sbbm3_.id " +
                "  left outer join T_SYS_GSJG sbgs2_ on this_.SBGS_ID = sbgs2_.id left outer join T_SYS_YG sbr4_ on this_.SBR_ID = sbr4_.id ";
        String sqlBase = "select distinct (select sum(t.SBSL) from T_BIZ_CGDMX t where t.CGD_ID = this_.ID and this_.DELETED != 1) as \"cgsl\", " +
                " (select sum(t.HSJ * t.SBSL) from T_BIZ_CGDMX t where t.CGD_ID = this_.ID and this_.DELETED != 1) as \"cgje\"," +
                " (select sum(t.BHSJ * t.SBSL) from T_BIZ_CGDMX t where t.CGD_ID = this_.ID and this_.DELETED != 1) as \"cgbhsje\"," +
                " (select sum(t.JE) from T_BIZ_GLFY t where t.CGD_ID = this_.ID and this_.DELETED != 1) as \"glfyje\"," +
                " this_.id as \"id\", this_.ysh as \"ysh\", gys1_.id as \"gys_id\", gys1_.qc as \"gys_qc\",  gys1_.jc as \"gys_jc\"," +
                " this_.cgdh as \"cgdh\", this_.cglx as \"cglx\",this_.fhrq as \"fhrq\", this_.jhdhrq as \"jhdhrq\", case when this_.jhdhrq < sysdate then 'ygq' when this_.jhdhrq >= sysdate then 'wgq' end \"sfgq\"," +
                " this_.cgsj as \"cgsj\", this_.yrk as \"yrk\", sbgs2_.id as \"sbgs_id\", sbgs2_.jc as \"sbgs_jc\", sbbm3_.id as \"sbbm_id\"," +
                " sbbm3_.qc as \"sbbm_qc\", sbr4_.id as \"sbr_id\", sbr4_.xm as \"sbr_xm\", cggs5_.id as \"cggs_id\", cggs5_.qc as \"cggs_qc\"," +
                " cggs5_.jc as \"cggs_jc\", cgbm6_.id as \"cgbm_id\", cgbm6_.qc as \"cgbm_qc\", cgr7_.id as \"cgr_id\", cgr7_.xm as \"cgr_xm\"," +
                " dzck8_.id as \"dzck_id\", dzck8_.mc as \"dzck_mc\", createuser9_.xm as \"createUser_xm\", this_.sftzd as \"sftzd\",this_.zt as \"zt\", this_.date_created as \"cjsj\" " +
                " from T_BIZ_CGD this_ left outer join T_SYS_YG createuser9_ on this_.CREATEUSER_ID = createuser9_.id " +
                " left outer join T_BIZ_CGDMX mx on mx.cgd_id=this_.id and mx.deleted!=1 " + regionSql +
                " left outer join T_SYS_GSJG cgbm6_ on this_.CGBM_ID = cgbm6_.id left outer join T_SYS_GSJG cggs5_ on this_.CGGS_ID = cggs5_.id " +
                " left outer join T_SYS_YG cgr7_ on this_.CGR_ID = cgr7_.id left outer join T_SYS_CK dzck8_ on this_.DZCK_ID = dzck8_.id " +
                " left outer join T_SYS_GSJG gys1_ on this_.GYS_ID = gys1_.id  left outer join T_SYS_GSJG sbbm3_ on this_.SBBM_ID = sbbm3_.id " +
                " left outer join T_SYS_GSJG sbgs2_ on this_.SBGS_ID = sbgs2_.id left outer join T_SYS_YG sbr4_  on this_.SBR_ID = sbr4_.id";
        if (StringUtils.isNotEmpty(wzbm)) {
            condition.add(" this_.id in (select cgd_id from T_BIZ_CGDMX cgdmx1_ where cgdmx1_.deleted!=1 and cgdmx1_.gg_id in (select gg.id from T_SYS_GG gg where gg.code like :code or gg.oldcode like :oldcode and gg.deleted!=1)) ");
            param.put("code", wzbm + "%");
            param.put("oldcode", wzbm + "%");
        }
        if (StringUtils.isNotEmpty(cgdh)) {
            condition.add(" this_.cgdh  like :cgdh ");
            param.put("cgdh", "%" + cgdh + "%");
        }
        if (StringUtils.isNotEmpty(zdr)) {
            condition.add(" createuser9_.xm  like :xm ");
            param.put("xm", "%" + zdr + "%");
        }
        if (fhrqStart != null) {
            condition.add(" this_.DATE_CREATED>= :fhsjStart ");
            param.put("fhsjStart", sDaysAfterNowDate(fhrqStart));
        } else {
            condition.add(" this_.DATE_CREATED>= :fhsjStart ");
            param.put("fhsjStart", sDaysAfterNowDate(new Date()));
        }
        if (fhrqEnd != null) {
            condition.add(" this_.DATE_CREATED<= :fhsjEnd ");
            param.put("fhsjEnd", jDaysAfterNowDate(fhrqEnd));
        } else {
            condition.add(" this_.DATE_CREATED<= :fhsjEnd ");
            param.put("fhsjEnd", jDaysAfterNowDate(new Date()));
        }

        if (sfgq != null && sfgq != 0) {
            Date end = DaysAfterNowDate(new Date());
            if (sfgq == 1) {
                condition.add(" this_.jhdhrq>= :end ");
                param.put("end", end);
            } else {
                condition.add(" this_.jhdhrq< :end ");
                param.put("end", end);
            }
        }
        if (StringUtils.isNotEmpty(zt)) {
            if (zt != null && !zt.isEmpty() && "ybc".equals(zt)) {
                condition.add(" this_.zt  = :zt ");
                param.put("zt", zt);
            } else if (zt != null && !zt.isEmpty() && "ytj".equals(zt)) {
                condition.add(" this_.zt  = :zt ");
                param.put("zt", zt);
            } else if (zt != null && !zt.isEmpty() && "ysp".equals(zt)) {
                condition.add(" this_.zt  = :zt ");
                param.put("zt", zt);
            }
        }

        if (dzckId != null) {
            condition.add(" dzck8_.id  = :dzckId ");
            param.put("dzckId", dzckId);
        }
        if (cggsId != null) {
            condition.add(" sbgs2_.id  = :cggsId ");
            param.put("cggsId", cggsId);
        }
        if (cgbmId != null) {
            condition.add(" sbbm3_.id  = :cgbmId ");
            param.put("cgbmId", cgbmId);
        }
        if (cgrId != null) {
            condition.add(" sbr4_.id  = :cgrId ");
            param.put("cgrId", cgrId);
        }
        if (gysid != null) {
            condition.add(" gys1_.id  = :gysid ");
            param.put("gysid", gysid);
        }
        if (StringUtils.isNotEmpty(rkzt)) {
            condition.add(" this_.yrk  = :yrk ");
            param.put("yrk", rkzt.equals("true"));
        }

        condition.add(" this_.cglx  = 'bght' ");
        condition.add(" (this_.sftzd is null or this_.sftzd = 0) ");

        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = " where " + StringUtils.join(condition, " and ");
        String orderSql = " order by this_.id desc";

        String dataRegion = createSQLDataRegion(null, "this_");

        String countSql = sqlCountBase + whereSql + " and " + dataRegion;
        String resultSql = sqlBase + whereSql + " and " + dataRegion + orderSql;


        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;

    }

    public Page<Cgd> findAllCgdtzList(Integer sfgq, String wzbm, String cgdh, Long dzckId, Date fhrqStart, Date fhrqEnd, String cglx, Long cggsId, Long cgbmId, Long cgrId, String zt, String sftzd, String htType, Long gysid, String rkzt, int pageNo, int pageSize) throws ParseException {
        Page<Cgd> page = new Page<Cgd>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("id");

        DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Cgdmx.class);

        detachedCriteria.createAlias("gg", "gg", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("cgd.id").as("cgd.id")))
                .add(Restrictions.or(Restrictions.like("gg.code", wzbm, MatchMode.START), Restrictions.like("gg.oldcode", wzbm, MatchMode.START)));

        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("gys", "gys", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbgs", "sbgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbbm", "sbbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbr", "sbr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cggs", "cggs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgbm", "cgbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgr", "cgr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("dzck", "dzck", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("cgsl").as("cgsl"))
                                .add(Property.forName("cgje").as("cgje"))
                                .add(Property.forName("cgbhsje").as("cgbhsje"))
                                .add(Property.forName("glfyje").as("glfyje"))
                                .add(Property.forName("id").as("id"))
                                .add(Property.forName("ysh").as("ysh"))
                                .add(Property.forName("gys.id").as("gys_id"))
                                .add(Property.forName("gys.qc").as("gys_qc"))
                                .add(Property.forName("gys.jc").as("gys_jc"))
                                .add(Projections.sqlProjection("this_.cgdh as cgdh", new String[]{"cgdh"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlProjection("this_.cglx as cglx", new String[]{"cglx"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlProjection("this_.fhrq as fhrq", new String[]{"fhrq"}, new Type[]{Hibernate.DATE}))
                                .add(Projections.sqlProjection("this_.jhdhrq as jhdhrq", new String[]{"jhdhrq"}, new Type[]{Hibernate.DATE}))
                                .add(Projections.sqlProjection("this_.cgsj as cgsj", new String[]{"cgsj"}, new Type[]{Hibernate.DATE}))
                                .add(Projections.sqlProjection("this_.yrk as yrk", new String[]{"yrk"}, new Type[]{Hibernate.STRING}))
                                .add(Property.forName("sbgs.id").as("sbgs_id"))
                                .add(Property.forName("sbgs.jc").as("sbgs_jc"))
                                .add(Property.forName("sbbm.id").as("sbbm_id"))
                                .add(Property.forName("sbbm.qc").as("sbbm_qc"))
                                .add(Property.forName("sbr.id").as("sbr_id"))
                                .add(Property.forName("sbr.xm").as("sbr_xm"))
                                .add(Property.forName("cggs.id").as("cggs_id"))
                                .add(Property.forName("cggs.qc").as("cggs_qc"))
                                .add(Property.forName("cggs.jc").as("cggs_jc"))
                                .add(Property.forName("cgbm.id").as("cgbm_id"))
                                .add(Property.forName("cgbm.qc").as("cgbm_qc"))
                                .add(Property.forName("cgr.id").as("cgr_id"))
                                .add(Property.forName("cgr.xm").as("cgr_xm"))
                                .add(Property.forName("dzck.id").as("dzck_id"))
                                .add(Property.forName("dzck.mc").as("dzck_mc"))
                                .add(Property.forName("tzsqzt").as("tzsqzt"))
                                .add(Property.forName("createUser.xm").as("createUser_xm"))
                                .add(Projections.sqlProjection("this_.sftzd as sftzd", new String[]{"sftzd"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlProjection("this_.zt as zt", new String[]{"zt"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlProjection("this_.date_created as cjsj", new String[]{"cjsj"}, new Type[]{Hibernate.DATE}))
                )
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (StringUtils.isNotEmpty(wzbm)) {
            criteria.add(Property.forName("id").in(detachedCriteria));
        }
        if (StringUtils.isNotEmpty(cgdh)) {
            criteria.add(Property.forName("cgdh").like(cgdh, MatchMode.ANYWHERE));
        }
        if (fhrqStart != null) {
            criteria.add(Restrictions.ge("dateCreated", sDaysAfterNowDate(fhrqStart)));
        } else {
            Date start = sDaysAfterNowDate(new Date());
            criteria.add(Restrictions.ge("dateCreated", start));
        }
        if (fhrqEnd != null) {
            criteria.add(Restrictions.le("dateCreated", jDaysAfterNowDate(fhrqEnd)));
        } else {
            Date end = jDaysAfterNowDate(new Date());
            criteria.add(Restrictions.le("dateCreated", end));
        }
        if (sfgq != null && sfgq != 0) {
            Date end = DaysAfterNowDate(new Date());
            if (sfgq == 1) {
                criteria.add(Restrictions.ge("jhdhrq", end));
            } else {
                criteria.add(Restrictions.lt("jhdhrq", end));
            }
        }
        if (StringUtils.isNotEmpty(zt)) {
            if (zt != null && !zt.isEmpty() && "wcx".equals(zt)) {
                criteria.add(Restrictions.or(Restrictions.isNull("sftzd"), Restrictions.eq("sftzd", false)));
            } else if (zt != null && !zt.isEmpty() && "ycx".equals(zt)) {
                criteria.add(Restrictions.eq("sftzd", true));
            }
        }

//        if (StringUtils.isNotEmpty(htType)) {
//            criteria.add(Restrictions.eq("cglx", htType));
//        }
//        } else {
//            criteria.add(Restrictions.ne("cglx", "bght"));
//        }

        if (StringUtils.isNotEmpty(cglx)) {
            criteria.add(Restrictions.eq("cglx", cglx));
        }
        if (dzckId != null) {
            criteria.add(Restrictions.eq("dzck.id", dzckId));
        }
        if (cggsId != null) {
            criteria.add(Restrictions.eq("sbgs.id", cggsId));
        }
        if (cgbmId != null) {
            criteria.add(Restrictions.eq("sbbm.id", cgbmId));
        }
        if (cgrId != null) {
            criteria.add(Restrictions.eq("sbr.id", cgrId));
        }
        if (gysid != null) {
            criteria.add(Restrictions.eq("gys.id", gysid));
        }
        if (StringUtils.isNotEmpty(rkzt)) {
            criteria.add(Restrictions.eq("yrk", rkzt.equals("true")));
        }

        if (sftzd != null) {
            criteria.add(Restrictions.or(Restrictions.isNull("sftzd"), Restrictions.eq("sftzd", false)));
        } else {
            criteria.add(Restrictions.eq("zt", "ysp"));
            criteria.add(Restrictions.or(Restrictions.isNull("yjs"), Restrictions.eq("yjs", false)));
        }

        createCgdDataRegion(criteria);
        Page list = this.findByCriteria(page, criteria);
        return list;
    }


    public List<Map> findSumCgd(Integer sfgq, String wzbm, String cgdh, Long dzckId, Date fhrqStart, Date fhrqEnd, String cglx, Long cggsId, Long cgbmId, Long cgrId, String zt, String sftzd, String htType, Long gysid, String rkzt, String zdr, Boolean dlkhcgd) {
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String sqlbase = "select sum(cgsl) as \"cgsl\",sum(cgje) as \"cgje\",sum(gylfyje) as \"gylfyje\" from (";

        String sql1 = "select sum(cgdmx6_.sbsl) as cgsl, sum(cgdmx6_.hsj*cgdmx6_.sbsl) as cgje," +
                "0 as gylfyje from t_biz_cgd t " +
                " left outer join T_BIZ_CGDMX cgdmx6_ on t.ID = cgdmx6_.CGD_ID and cgdmx6_.deleted!=1";

        String sql2 = "select 0 as cgsl, 0 as cgje,sum(glfy5_.je) as gylfyje from t_biz_cgd t " +
                " left outer join T_BIZ_GLFY glfy5_ on t.ID = glfy5_.CGD_ID and glfy5_.deleted!=1";

        if (StringUtils.isNotEmpty(wzbm)) {
            condition.add(" t.id in (select cgd_id from T_BIZ_CGDMX mx where mx.gg_id in (select gg.id from T_SYS_GG gg where gg.code like :code or gg.oldcode like :oldcode and gg.deleted!=1)) ");
            param.put("code", wzbm);
            param.put("oldcode", wzbm);
        }

        if (StringUtils.isNotEmpty(cgdh)) {
            condition.add(" t.cgdh like :cgdh ");
            param.put("cgdh", "%" + cgdh + "%");
        }


        if (fhrqStart != null) {
            condition.add(" t.DATE_CREATED>= :fhsjStart ");
            param.put("fhsjStart", sDaysAfterNowDate(fhrqStart));
        } else {
            condition.add(" t.DATE_CREATED>= :fhsjStart ");
            param.put("fhsjStart", sDaysAfterNowDate(7));
        }
        if (fhrqEnd != null) {
            condition.add(" t.DATE_CREATED<= :fhsjEnd ");
            param.put("fhsjEnd", jDaysAfterNowDate(fhrqEnd));
        } else {
            condition.add(" t.DATE_CREATED<= :fhsjEnd ");
            param.put("fhsjEnd", jDaysAfterNowDate(new Date()));
        }

        if (sfgq != null && sfgq != 0) {
            Date end = DaysAfterNowDate(new Date());
            if (sfgq == 1) {
                condition.add(" t.jhdhrq>= :end ");
                param.put("end", end);
            } else {
                condition.add(" t.jhdhrq< :end ");
                param.put("end", end);
            }
        }

        if (zt != null && !zt.isEmpty() && "ybc".equals(zt)) {
            condition.add(" t.zt=:zt ");
            param.put("zt", zt);
        } else if (zt != null && !zt.isEmpty() && "ytj".equals(zt)) {
            condition.add(" t.zt=:zt ");
            param.put("zt", zt);
        } else if (zt != null && !zt.isEmpty() && "ysp".equals(zt)) {
            condition.add(" t.zt=:zt ");
            param.put("zt", zt);
        }

        if (StringUtils.isNotEmpty(htType) && htType.equals("bght")) {
            condition.add(" t.cglx=:cglx ");
            param.put("cglx", htType);
        } else {
            condition.add(" t.cglx!='bght' ");
        }

        if (StringUtils.isNotEmpty(rkzt)) {
            condition.add(" t.yrk=:yrk ");
            param.put("yrk", rkzt.equals("true"));
        }

        if (StringUtils.isNotEmpty(zdr)) {
            condition.add(" t.createuser_id in (select id from T_SYS_YG yg where yg.xm like :zdr and yg.deleted!=1)");
            param.put("zdr", "%" + zdr + "%");
        }

        if (StringUtils.isNotEmpty(cglx)) {
            condition.add(" t.cglx=:cglx ");
            param.put("cglx", cglx);
        }
        if (dzckId != null) {
            condition.add(" t.dzck_id=:dzckId ");
            param.put("dzckId", dzckId);
        }

        if (cggsId != null) {
            condition.add(" t.sbgs_id=:cggs ");
            param.put("cggs", cggsId);
        }
        if (cgbmId != null) {
            condition.add(" t.sbbm_id=:cgbm ");
            param.put("cgbm", cgbmId);
        }
        if (cgrId != null) {
            condition.add(" t.sbr_id=:sbr ");
            param.put("sbr", cgrId);
        }

        if (gysid != null) {
            condition.add(" t.gys_id=:gys ");
            param.put("gys", gysid);
        }

        if (sftzd != null) {
            condition.add(" (t.sftzd is null or t.sftzd=0) ");
        } else {
            condition.add(" t.zt='ysp' and (t.yjs is null or t.yjs=0) ");
        }

        if (dlkhcgd != null && dlkhcgd) {
            condition.add(" t.sbgs_id in (select id from T_SYS_GSJG where type='nbgs' and deleted!=1) ");
        }

        condition.add(" t.cglx<>'nbcg' ");

        String dataRegion = createSQLDataRegion(null, "t");

        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = StringUtils.join(condition, " and ") + " and ";

        sql1 += " where t.deleted!=1 and " + whereSql + dataRegion;
        sql2 += " where t.deleted!=1 and " + whereSql + dataRegion;

        String sql = sqlbase + sql1 + " union all " + sql2 + ")";

        SQLQuery sqlQuery = createSQLQuery(sql);
        List<Map> list = sqlQuery
                .setProperties(param)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();
        return list;
    }

    public List<Map> findSumBgCgd(Integer sfgq, String wzbm, String cgdh, Long dzckId, Date fhrqStart, Date fhrqEnd, String cglx, Long cggsId, Long cgbmId, Long cgrId, String zt, String sftzd, String htType, Long gysid, String rkzt, String zdr) {
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();

        String regionSql = StringUtils.EMPTY;
        if (existsSQLQuery("select 1 from t_sys_yg_bmfw where yg_id = ?", YgService.getCurrentYg().getId())) {
            regionSql += "  inner join v_wzbm_gg_yg_search bmfw  on cgdmx6_.GG_ID = bmfw.ggid and bmfw.yg_id=" + YgService.getCurrentYg().getId();
        } else if (existsSQLQuery("select 1 from v_wzbm_gg_search  where gsid = ?", YgService.getCurrentYgGsId())) {
            regionSql += "  inner join v_wzbm_gg_search bmfw on cgdmx6_.GG_ID = bmfw.ggid and bmfw.gsid=" + YgService.getCurrentYgGsId();
        }

        String sqlbase = "select sum(cgsl) as \"cgsl\",sum(cgje) as \"cgje\",sum(gylfyje) as \"gylfyje\" from (";

        String sql1 = "select sum(cgdmx6_.sbsl) as cgsl, sum(cgdmx6_.hsj*cgdmx6_.sbsl) as cgje," +
                "0 as gylfyje from t_biz_cgd t " +
                " left outer join T_BIZ_CGDMX cgdmx6_ on t.ID = cgdmx6_.CGD_ID and cgdmx6_.deleted!=1 " + regionSql;

        String sql2 = "select 0 as cgsl, 0 as cgje,sum(glfy5_.je) as gylfyje from t_biz_cgd t " +
                " left outer join T_BIZ_CGDMX cgdmx6_ on t.ID = cgdmx6_.CGD_ID and cgdmx6_.deleted!=1 " + regionSql +
                " left outer join T_BIZ_GLFY glfy5_ on t.ID = glfy5_.CGD_ID and glfy5_.deleted!=1 ";

        if (StringUtils.isNotEmpty(wzbm)) {
            condition.add(" t.id in (select cgd_id from T_BIZ_CGDMX mx where mx.gg_id in (select gg.id from T_SYS_GG gg where gg.code like :code or gg.oldcode like :oldcode and gg.deleted!=1)) ");
            param.put("code", wzbm);
            param.put("oldcode", wzbm);
        }

        if (StringUtils.isNotEmpty(cgdh)) {
            condition.add(" t.cgdh like :cgdh ");
            param.put("cgdh", "%" + cgdh + "%");
        }

        if (fhrqStart != null) {
            condition.add(" t.DATE_CREATED>= :fhsjStart ");
            param.put("fhsjStart", sDaysAfterNowDate(fhrqStart));
        } else {
            condition.add(" t.DATE_CREATED>= :fhsjStart ");
            param.put("fhsjStart", sDaysAfterNowDate(new Date()));
        }
        if (fhrqEnd != null) {
            condition.add(" t.DATE_CREATED<= :fhsjEnd ");
            param.put("fhsjEnd", jDaysAfterNowDate(fhrqEnd));
        } else {
            condition.add(" t.DATE_CREATED<= :fhsjEnd ");
            param.put("fhsjEnd", jDaysAfterNowDate(new Date()));
        }

        if (sfgq != null && sfgq != 0) {
            Date end = DaysAfterNowDate(new Date());
            if (sfgq == 1) {
                condition.add(" t.jhdhrq>= :end ");
                param.put("end", end);
            } else {
                condition.add(" t.jhdhrq< :end ");
                param.put("end", end);
            }
        }

        if (zt != null && !zt.isEmpty() && "ybc".equals(zt)) {
            condition.add(" t.zt=:zt ");
            param.put("zt", zt);
        } else if (zt != null && !zt.isEmpty() && "ytj".equals(zt)) {
            condition.add(" t.zt=:zt ");
            param.put("zt", zt);
        } else if (zt != null && !zt.isEmpty() && "ysp".equals(zt)) {
            condition.add(" t.zt=:zt ");
            param.put("zt", zt);
        }


        if (StringUtils.isNotEmpty(rkzt)) {
            condition.add(" t.yrk=:yrk ");
            param.put("yrk", rkzt.equals("true"));
        }

        if (StringUtils.isNotEmpty(zdr)) {
            condition.add(" t.createuser_id in (select id from T_SYS_YG yg where yg.xm like :zdr and yg.deleted!=1)");
            param.put("zdr", "%" + zdr + "%");
        }


        if (dzckId != null) {
            condition.add(" t.dzck_id=:dzckId ");
            param.put("dzckId", dzckId);
        }

        if (cggsId != null) {
            condition.add(" t.sbgs_id=:cggs ");
            param.put("cggs", cggsId);
        }
        if (cgbmId != null) {
            condition.add(" t.sbbm_id=:cgbm ");
            param.put("cgbm", cgbmId);
        }
        if (cgrId != null) {
            condition.add(" t.sbr_id=:sbr ");
            param.put("sbr", cgrId);
        }

        if (gysid != null) {
            condition.add(" t.gys_id=:gys ");
            param.put("gys", gysid);
        }

        condition.add(" t.cglx='bght' ");
        condition.add(" (t.sftzd is null or t.sftzd=0) ");
        String dataRegion = createSQLDataRegion(null, "t");

        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = StringUtils.join(condition, " and ") + " and ";

        sql1 += " where t.deleted!=1 and " + whereSql + dataRegion;
        sql2 += " where t.deleted!=1 and " + whereSql + dataRegion;

        String sql = sqlbase + sql1 + " union all " + sql2 + ")";

        SQLQuery sqlQuery = createSQLQuery(sql);
        List<Map> list = sqlQuery
                .setProperties(param)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();
        return list;
    }

    private void createCgdDataRegion(Criteria criteria) {
        criteria.createAlias("createUser", "createUser", CriteriaSpecification.LEFT_JOIN);
        List<Ck> list = this.ygDao.get(YgService.getCurrentYg().getId()).getCkList();
        if (list != null && list.size() > 0) {
            switch (YgService.getCurrentYgCxfw()) {
                case 0:
                    break;
                case 1:
                    criteria.add(Restrictions.in("dzck", list));
                    break;
                case 2:
                    criteria.add(Restrictions.in("dzck", list));
                    break;
                case 3:
                    criteria.add(Restrictions.in("dzck", list));
                    break;
            }
        } else {
            SimpleExpression se = null;
            switch (YgService.getCurrentYgCxfw()) {
                case 0:
                    return;
                case 1:
                    se = Restrictions.eq("createOrgan.id", YgService.getCurrentYgZgGsId());
                    break;
                case 2:
                    se = Restrictions.eq("createDepartment.id", YgService.getCurrentYgGsId());
                    break;
                case 3:
                    se = Restrictions.eq("createUser.id", YgService.getCurrentYg().getId());
                    break;
            }
            criteria.add(
                    Restrictions.or(se,
                            Restrictions.eq("sbgs.id", YgService.getCurrentYgZgGsId())
                    )
            );
        }
    }


    private String createSQLDataRegion(String joinStr, String alias) {
        List<Ck> list = this.ygDao.get(YgService.getCurrentYg().getId()).getCkList();
        if (list != null && list.size() > 0) {
            String ids = "";
            for (Ck ck : list) {
                ids += ck.getId() + ",";
            }
            ids = ids.substring(0, ids.length() - 1);


            alias = StringUtils.isEmpty(alias) ? "" : alias + ".";
            joinStr = StringUtils.isEmpty(joinStr) ? "" : joinStr + " and ";
            String regionSql = "";
            switch (YgService.getCurrentYgCxfw()) {
                case 0:
                    regionSql += " {0} 1=1";
                    break;
                case 1:
                    regionSql += " {0} ({1}dzck_id in (" + ids + "))";
                    break;
                case 2:
                    regionSql += " {0} ({1}dzck_id in (" + ids + "))";
                    break;
                case 3:
                    regionSql += " {0} ({1}dzck_id in (" + ids + "))";
                    break;
            }
            return regionSql.replace("{0}", joinStr).replace("{1}", alias);
        } else {
            alias = StringUtils.isEmpty(alias) ? "" : alias + ".";
            joinStr = StringUtils.isEmpty(joinStr) ? "" : joinStr + " and ";
            String regionSql = "";
            switch (YgService.getCurrentYgCxfw()) {
                case 0:
                    regionSql += " {0} 1=1";
                    break;
                case 1:
                    regionSql += " {0} ( {1}CREATEORGAN_ID =" + YgService.getCurrentYgZgGsId();
                    break;
                case 2:
                    regionSql += "  {0} ( {1}CREATEDEPARTMENT_ID =" + YgService.getCurrentYgGsId();
                    break;
                case 3:
                    regionSql += "  {0} ( {1}CREATEDEPARTMENT_ID =" + YgService.getCurrentYgGsId();
                    regionSql += "  and   {1}createuser_id =" + YgService.getCurrentYg().getId();
                    break;
            }

            if (YgService.getCurrentYgCxfw() != 0) {
                regionSql += " or {1}sbgs_id =" + YgService.getCurrentYgZgGsId() + ")";
            }

            return regionSql.replace("{0}", joinStr).replace("{1}", alias);
        }
    }

    private String createCkDataRegion(String joinStr, String alias) {
        List<Ck> list = this.ygDao.get(YgService.getCurrentYg().getId()).getCkList();
        if (list != null && list.size() > 0) {
            String ids = "";
            for (Ck ck : list) {
                ids += ck.getId() + ",";
            }
            ids = ids.substring(0, ids.length() - 1);


            String aliasName = StringUtils.isEmpty(alias) ? "" : alias + ".";
            String regionHql = "";
            switch (YgService.getCurrentYgCxfw()) {
                case 0:
                    regionHql += " {0} 1=1";
                    break;
                case 1:
                    regionHql += " {0} ({1}dzck.id in (" + ids + "))";
                    break;
                case 2:
                    regionHql += " {0} ({1}dzck.id in (" + ids + "))";
                    break;
                case 3:
                    regionHql += " {0} ({1}dzck.id in (" + ids + "))";
                    break;
            }
            return regionHql.replace("{0}", joinStr).replace("{1}", aliasName);
        } else {
            String aliasName = StringUtils.isEmpty(alias) ? "" : alias + ".";
            String regionHql = "";
            switch (YgService.getCurrentYgCxfw()) {
                case 0:
                    regionHql += " {0} ( 1=1";
                    break;
                case 1:
                    regionHql += " {0} ({1}createOrgan.id =" + YgService.getCurrentYgZgGsId();
                    break;
                case 2:
                    regionHql += " ({0} ({1}createDepartment.id =" + YgService.getCurrentYgGsId();
                    break;
                case 3:
                    regionHql += " {0} ({1}createDepartment.id =" + YgService.getCurrentYgGsId();
                    regionHql += " {0} {1}createUser.id =" + YgService.getCurrentYg().getId();
                    break;
            }

            regionHql += " or {1}sbgs.id=" + YgService.getCurrentYgZgGsId() + ")";
            return regionHql.replace("{0}", joinStr).replace("{1}", aliasName);
        }

    }

    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    public Date jDaysAfterNowDate(Date date) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(date);
        rightNow.set(java.util.Calendar.HOUR_OF_DAY, 23);
        rightNow.set(java.util.Calendar.MINUTE, 59);
        rightNow.set(java.util.Calendar.SECOND, 59);
        return rightNow.getTime();
    }

    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    public Date sDaysAfterNowDate(Date date) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(date);
        rightNow.set(java.util.Calendar.HOUR_OF_DAY, 0);
        rightNow.set(java.util.Calendar.MINUTE, 0);
        rightNow.set(java.util.Calendar.SECOND, 0);
        return rightNow.getTime();
    }

    public Date DaysAfterNowDate(Date date) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(date);
        rightNow.add(Calendar.DAY_OF_MONTH, -1);
        rightNow.set(java.util.Calendar.HOUR_OF_DAY, 23);
        rightNow.set(java.util.Calendar.MINUTE, 59);
        rightNow.set(java.util.Calendar.SECOND, 59);
        return rightNow.getTime();
    }

    public Page<Map> findByYtjQuery(Long dhck, String yrk, String sfys, String lx, Long cggsId, Long cgbmId, Long cgrId, String cgdh, String wzbm, Long gysid, Date fhrqStart, Date fhrqEnd, Integer sfgq, int pageNo, int pageSize) {
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        Page<Map> page = new Page<Map>();

//        String regionSql = StringUtils.EMPTY;
//        if (existsSQLQuery("select 1 from t_sys_yg_bmfw where yg_id = ?", YgService.getCurrentYg().getId())) {
//            regionSql += "  inner join v_wzbm_gg_yg_search bmfw  on cgdmx6_.GG_ID = bmfw.ggid and bmfw.yg_id=" + YgService.getCurrentYg().getId();
//        } else if (existsSQLQuery("select 1 from v_wzbm_gg_search  where gsid = ?", YgService.getCurrentYgGsId())) {
//            regionSql += "  inner join v_wzbm_gg_search bmfw on cgdmx6_.GG_ID = bmfw.ggid and bmfw.gsid=" + YgService.getCurrentYgGsId();
//        }

        //=====================================
        if (cgdh != null && !cgdh.isEmpty()) {
            condition.add(" cgd0_.cgdh like :cgdh ");
            param.put("cgdh", "%" + cgdh.trim() + "%");
        }
        if (sfys != null && !sfys.isEmpty()) {
            condition.add(" cgd0_.sfys = :sfys");
            param.put("sfys", "true".equals(sfys));
        }

        if (yrk.isEmpty()) {
            condition.add(" cgdmx6_.rksl is not null");
        } else {
            if (yrk != null && !yrk.isEmpty()) {
                if ("true".equals(yrk)) {
                    condition.add(" (cgd0_.yrk = :yrk or cgd0_.rksj is not null) ");
                    param.put("yrk", "true".equals(yrk));
                } else {
                    condition.add(" cgd0_.yrk = :yrk and cgd0_.zt!='ytj'");
                    param.put("yrk", "true".equals(yrk));
                }
            }
        }

        if (lx != null && !lx.isEmpty()) {
            condition.add(" cgd0_.cglx = :cglx");
            param.put("cglx", lx);
        }
        if (dhck != null) {
            condition.add(" cgd0_.dzck_id = :dzck ");
            param.put("dzck", dhck);
        }
        if (cggsId != null) {
            condition.add(" cgd0_.sbgs_id = :sbgsid ");
            param.put("sbgsid", cggsId);
        }
        if (cgbmId != null) {
            condition.add(" cgd0_.sbbm_id = :sbbmid ");
            param.put("sbbmid", cgbmId);
        }
        if (cgrId != null) {
            condition.add(" cgd0_.sbr_id = :sbrid ");
            param.put("sbrid", cgrId);
        }

        if (wzbm != null && !wzbm.isEmpty()) {
            condition.add(" cgd0_.id in (select cgdmx.cgd_id from T_BIZ_CGDMX cgdmx where cgdmx.gg_id in (select id from T_SYS_GG gg where gg.code like :code or gg.oldcode like :oldcode))");
            param.put("code", wzbm + "%");
            param.put("oldcode", wzbm + "%");
        }

        if (sfgq != null && sfgq != 0) {
            Date end = DaysAfterNowDate(new Date());
            if (sfgq == 1) {
                condition.add(" cgd0_.jhdhrq>= :end ");
                param.put("end", end);
            } else {
                condition.add(" cgd0_.jhdhrq< :end ");
                param.put("end", end);
            }
        }

        if (gysid != null) {
            condition.add(" cgd0_.gys_id = :gysid ");
            param.put("gysid", gysid);
        }

        if (fhrqStart != null) {
            condition.add(" cgd0_.rksj >= :rksj ");
            param.put("rksj", sDaysAfterNowDate(fhrqStart));
        }

        if (fhrqEnd != null) {
            condition.add(" cgd0_.rksj <= :rksjend ");
            param.put("rksjend", jDaysAfterNowDate(fhrqEnd));
        }

        //==============记录数量===============
        String sqlCountBase = "select count(distinct cgd0_.id) from T_BIZ_CGD cgd0_ left outer join T_BIZ_HT cght1_ on cgd0_.CGHT_ID = cght1_.id " +
                " left outer join T_BIZ_CGDMX cgdmx6_ on cgd0_.ID = cgdmx6_.CGD_ID and cgdmx6_.deleted!=1 "; //+ regionSql;

        //==============查询结果===============
        String sqlBase = "select distinct cgd0_.id as \"id\",cgd0_.cgdh as \"cgdh\",cgd0_.CGGS_ID as \"cggs.id\",cggs5_.jc as \"cggs_jc\"," +
                " cgd0_.cglx as \"cglx\", cgd0_.fhrq as \"fhrq\",cgd0_.jhdhrq as \"dhrq\", cgd0_.GYS_ID as \"gys.id\",gys1_.jc as \"gys_jc\",cgd0_.rksj as \"rksj\"," +
                " cgd0_.SBBM_ID as \"sbbm.id\",cgd0_.SBGS_ID as \"sbgs.id\",sbgs2_.jc as \"sbgs_jc\",sbgs2_.qc as \"sbgs_qc\",cgd0_.date_created as \"date_created\", " +
                " cgd0_.SBR_ID as \"sbr.id\",sbr4_.xm as \"sbr_xm\",sbbm3_.jc as \"sbbm_jc\",sbbm3_.qc as \"sbbm_qc\"," +
                " cgd0_.sfys as \"sfys\",cgd0_.yjs as \"yjs\",cgd0_.yrk as \"yrk\",cgd0_.ysh as \"ysh\",cgd0_.zt as \"zt\"," +
                " case when (cgd0_.jhdhrq >= sysdate-1) then 'true' else 'false' end as \"sfgq\"," +
                " (select sum(t.BHSJ * t.SBSL) from T_BIZ_CGDMX t where t.CGD_ID = cgd0_.ID and cgd0_.DELETED != 1) as \"bhse\"," +
                " (select sum(t.HSJ * t.SBSL) from T_BIZ_CGDMX t where t.CGD_ID = cgd0_.ID and cgd0_.DELETED != 1) as \"hse\"," +
                " (select sum(t.SBSL) from T_BIZ_CGDMX t where t.CGD_ID = cgd0_.ID and cgd0_.DELETED != 1) as \"sbsl\"," +
                " (select sum(t.JE) from T_BIZ_GLFY t where t.CGD_ID = cgd0_.ID and cgd0_.DELETED != 1) as \"glfyje\"" +
                "  from T_BIZ_CGD cgd0_  left outer join T_SYS_GSJG cggs5_ on cgd0_.CGGS_ID = cggs5_.id " +
                "  left outer join T_BIZ_HT cght1_ on cgd0_.CGHT_ID = cght1_.id " +
                " left outer join T_BIZ_CGDMX cgdmx6_ on cgd0_.ID = cgdmx6_.CGD_ID and cgdmx6_.deleted!=1 " + //regionSql +
                " left outer join T_SYS_GSJG gys1_ on cgd0_.GYS_ID = gys1_.id  left outer join T_SYS_GSJG sbbm3_ on cgd0_.SBBM_ID = sbbm3_.id " +
                " left outer join T_SYS_GSJG sbgs2_ on cgd0_.SBGS_ID = sbgs2_.id left outer join T_SYS_YG sbr4_  on cgd0_.SBR_ID = sbr4_.id";


        String whereSql = " where (cgd0_.deleted != '1') and cgd0_.zt <> 'ybc' and (cgd0_.cglx <> 'zddh' or cgd0_.cglx = 'zddh' and cght1_.rzlx = '2')";
        if (!condition.isEmpty())
            whereSql += " and " + StringUtils.join(condition, " and ");
        String orderSql = " order by cgd0_.rksj desc,cgd0_.date_created desc";

        String dataRegion = createSQLDataRegion(null, "cgd0_");

        String countSql = sqlCountBase + whereSql + " and " + dataRegion;
        String resultSql = sqlBase + whereSql + " and " + dataRegion + orderSql;


        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }


    public Page<Cgd> findCgjlQuery(Long cghtId, int pageNo, int pageSize) {
        String hql = null;
        List<Object> param = new ArrayList<Object>();
        StringBuilder condition = new StringBuilder();
        Page<Cgd> page = new Page<Cgd>(pageNo, pageSize, 0);

        if (cghtId != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" cght.id = ? ");
            param.add(cghtId);
        }


        //==============记录数量===============
        hql = "select count(*) from Cgd where 1 =1 " + RegionDao.createDataRegion("and");
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        Long c = null;
        if (param.size() != 0) {
            c = (Long) findUnique(hql, param.toArray());
            page.setTotalCount(c.intValue());
        } else {
            c = (Long) findUnique(hql);
            page.setTotalCount(c.intValue());
        }
        if (c == 0)
            return page;
        //==============查询结果===============
        hql = "from Cgd where 1=1  " + RegionDao.createDataRegion("and");
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        if (param.size() != 0)
            return this.find(page, hql, param.toArray());
        return find(page, hql);
    }


    /**
     * 条件查询采购单
     *
     * @param cgdh
     * @param fhrqStart
     * @param fhrqEnd
     * @param cgjsdId
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Page<Cgd> findCgdSearch(String cgdh, Date fhrqStart, Date fhrqEnd, Long cgjsdId, Long cggsId, Long gysId, int pageNo, int pageSize) {
        Page<Cgd> page = new Page<Cgd>();
        page.setAutoCount(false);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("dateCreated");
        Criteria countCriteria = getSession().createCriteria(this.entityClass)
                .setProjection(Projections.rowCount());

        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("gys", "gys", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbgs", "sbgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbbm", "sbbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbr", "sbr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cggs", "cggs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgdmxList", "cgdmxList")
                .setProjection(Projections.projectionList()
                                .add(Projections.sum("cgdmxList.rksl").as("rksl"))
                                .add(Projections.sum("cgdmxList.sbsl").as("sbsl"))
                                .add(Projections.sum("cgdmxList.hsj").as("hsj"))
                                .add(Projections.sum("cgdmxList.sjdj").as("sjdj"))
                                .add(Projections.sum("cgdmxList.bhsj").as("bhsj"))
                                .add(Projections.groupProperty("id").as("id"))
                                .add(Projections.groupProperty("cght.id").as("cght_id"))
                                .add(Projections.sqlGroupProjection("cgdh as cgdh", "cgdh", new String[]{"cgdh"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlGroupProjection("fhrq as fhrq", "fhrq", new String[]{"fhrq"}, new Type[]{Hibernate.DATE}))
                                .add(Projections.groupProperty("gys.qc").as("gys_qc"))
                                .add(Projections.groupProperty("dsyfje").as("dsyfje"))
                                .add(Projections.groupProperty("sbgs.id").as("sbgs_id"))
                                .add(Projections.groupProperty("sbgs.jc").as("sbgs_jc"))
                                .add(Projections.groupProperty("sbbm.id").as("sbbm_id"))
                                .add(Projections.groupProperty("sbbm.qc").as("sbbm_qc"))
                                .add(Projections.groupProperty("sbr.id").as("sbr_id"))
                                .add(Projections.groupProperty("sbr.xm").as("sbr_xm"))
                                .add(Projections.groupProperty("cggs.id").as("cggs_id"))
                                .add(Projections.groupProperty("cggs.qc").as("cggs_qc"))
                                .add(Projections.groupProperty("cggs.jc").as("cggs_jc"))
                                .add(Projections.sqlGroupProjection("this_.zt as zt", "this_.zt", new String[]{"zt"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.groupProperty("dateCreated"))
                )
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (StringUtils.isNotEmpty(cgdh)) {
            criteria.add(Property.forName("cgdh").like(cgdh, MatchMode.START));
            countCriteria.add(Property.forName("cgdh").like(cgdh, MatchMode.START));
        }
        if (fhrqStart != null) {
            criteria.add(Restrictions.gt("fhrq", fhrqStart));
            countCriteria.add(Restrictions.gt("fhrq", fhrqStart));
        }
        if (fhrqEnd != null) {
            criteria.add(Restrictions.lt("fhrq", fhrqEnd));
            countCriteria.add(Restrictions.lt("fhrq", fhrqEnd));
        }
        if (gysId != null) {
            criteria.add(Restrictions.eq("gys.id", gysId));
            countCriteria.add(Restrictions.eq("gys.id", gysId));
        }
        if (cggsId != null) {
            criteria.add(Restrictions.eq("cggs.id", cggsId));
            countCriteria.add(Restrictions.eq("cggs.id", cggsId));
        }
        if (cgjsdId != null) {
            criteria.add(Restrictions.eq("cgjsd.id", cgjsdId));
            countCriteria.add(Restrictions.eq("cgjsd.id", cgjsdId));
        } else {
            DetachedCriteria dc = DetachedCriteria.forClass(Cgdmx.class);
            dc.setProjection(Projections.property("cgd.id"));
            dc.add(Restrictions.isNull("cgjsd"));
            dc.add(Restrictions.or(Restrictions.isNotNull("rksl"), Restrictions.ne("rksl", 0d)));
            criteria.add(Subqueries.propertyIn("id", dc));
            countCriteria.add(Subqueries.propertyIn("id", dc));
        }
        criteria.add(Restrictions.eq("yrk", true));
        countCriteria.add(Restrictions.eq("yrk", true));
        criteria.add(Restrictions.eq("sftzd", false));
        countCriteria.add(Restrictions.eq("sftzd", false));
//        RegionDao.createDataRegion(criteria);
//        RegionDao.createDataRegion(countCriteria);
        Page list = this.findByCriteria(page, criteria);
        Long totalCount = (Long) countCriteria.uniqueResult();
        list.setTotalCount(totalCount.intValue());
        return list;
    }


    public List<Map> findCgmx(Long id) {
        Criteria criteria = getSession().createCriteria(Cgdmx.class)
                .createAlias("gg", "gg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgd", "cgd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgjsd", "jsd", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("id").as("id"))
                                .add(Property.forName("cgd.cgdh").as("cgd.cgdh"))
                                .add(Property.forName("cgd.cglx").as("cgd.cglx"))
                                .add(Property.forName("gg.id").as("wzgg.id"))
                                .add(Property.forName("dhdStr").as("dhdStr"))
                                .add(Property.forName("gg.code").as("wzgg.code"))
                                .add(Property.forName("gg.oldcode").as("wzgg.oldcode"))
                                .add(Property.forName("gg.name").as("wzgg.name"))
                                .add(Property.forName("gg.th").as("wzgg.th"))
                                .add(Property.forName("gg.cz").as("wzgg.cz"))
                                .add(Projections.sqlProjection("gg1_.ggxh as \"wzgg.ggxh\"", new String[]{"wzgg.ggxh"}, new Type[]{Hibernate.STRING}))
//                                .add(Projections.sqlProjection("gg1_.cz||' '||gg1_.ggxh as \"wzgg.ggxh\"", new String[]{"wzgg.ggxh"}, new Type[]{Hibernate.STRING}))
                                .add(Property.forName("gg.dw").as("wzgg.dw"))
                                .add(Property.forName("ph").as("ph"))
                                .add(Property.forName("cd.id").as("cd.id"))
                                .add(Property.forName("cd.z").as("cd.z"))
                                .add(Property.forName("ck").as("ck"))
                                .add(Property.forName("js").as("js"))
                                .add(Property.forName("sbsl").as("sbsl"))
                                .add(Property.forName("rkjs").as("rkjs"))
                                .add(Property.forName("rkjz").as("rkjz"))
                                .add(Property.forName("sku").as("sku"))
                                .add(Property.forName("rksl").as("rksl"))
                                .add(Property.forName("rkhsj").as("rkhsj"))
                                .add(Property.forName("sssl").as("sssl"))
                                .add(Property.forName("ssjs").as("ssjs"))
                                .add(Property.forName("th").as("th"))
                                .add(Property.forName("kh.id").as("kh.id"))
                                .add(Property.forName("kh.khbm").as("kh.khbm"))
                                .add(Property.forName("jzfs.id").as("jzfs.id"))
                                .add(Property.forName("jzfs.z").as("jzfs.z"))
                                .add(Property.forName("hsj").as("hsj"))
                                .add(Property.forName("bhsj").as("bhsj"))
                                .add(Property.forName("bz").as("bz"))
                                .add(Property.forName("ph").as("ph"))
                                .add(Property.forName("jsd.jsdh").as("jsdh"))
                                .add(Property.forName("jsd.id").as("jsdId"))
                                .add(Property.forName("xsxt_xsdh").as("xsxt_xsdh"))
                                .add(Property.forName("xsxt_htbm").as("xsxt_htbm"))
                                .add(Property.forName("carNo").as("carNo"))
                                .add(Property.forName("bindNo").as("bindNo"))
                                .add(Projections.sqlProjection("mxh as \"mxh\"", new String[]{"mxh"}, new Type[]{Hibernate.LONG}))
                ).add(Restrictions.eq("cgd.id", id)).addOrder(Property.forName("gg.oldcode").asc())
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        criteria.addOrder(Property.forName("mxh").asc());
        return (List<Map>) criteria.list();
    }


    public List<Map> findCgjsdSearchCgmx(Long id) {
        Criteria criteria = getSession().createCriteria(Cgdmx.class)
                .createAlias("gg", "gg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgd", "cgd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgjsd", "cgjsd", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("id").as("id"))
                                .add(Projections.sqlProjection("cgd5_.id as \"cgd.id\"", new String[]{"cgd.id"}, new Type[]{Hibernate.LONG}))
                                .add(Property.forName("cgd.cgdh").as("cgd.cgdh"))
                                .add(Property.forName("cgd.dsyfje").as("cgd.dsyfje"))
                                .add(Property.forName("gg.id").as("wzgg.id"))
                                .add(Property.forName("gg.code").as("wzgg.code"))
                                .add(Property.forName("gg.name").as("wzgg.name"))
                                .add(Property.forName("gg.ggxh").as("wzgg.ggxh"))
                                .add(Projections.sqlProjection("gg1_.cz||' '||gg1_.ggxh as \"wzgg.ggxh\"", new String[]{"wzgg.ggxh"}, new Type[]{Hibernate.STRING}))
                                .add(Property.forName("cd.id").as("cd.id"))
                                .add(Property.forName("cd.z").as("cd.z"))
                                .add(Property.forName("ck").as("ck"))
                                .add(Property.forName("js").as("js"))
                                .add(Property.forName("sbsl").as("sbsl"))
                                .add(Property.forName("rkjs").as("rkjs"))
                                .add(Property.forName("rkjz").as("rkjz"))
                                .add(Property.forName("rksl").as("rksl"))
                                .add(Property.forName("rkhsj").as("rkhsj"))
                                .add(Property.forName("th").as("th"))
                                .add(Property.forName("kh.id").as("kh.id"))
                                .add(Property.forName("kh.khbm").as("kh.khbm"))
                                .add(Property.forName("jzfs.id").as("jzfs.id"))
                                .add(Property.forName("jzfs.z").as("jzfs.z"))
                                .add(Property.forName("hsj").as("hsj"))
                                .add(Property.forName("sjdj").as("sjdj"))
                                .add(Property.forName("bhsj").as("bhsj"))
                                .add(Property.forName("bz").as("bz"))
                ).add(Restrictions.eq("cgd.id", id)).add(Restrictions.isNull("cgjsd.id"))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        criteria.addOrder(Property.forName("gg.code").asc());
        return (List<Map>) criteria.list();
    }


    public List<Map> findCgfy(Long id) {
        Criteria criteria = getSession().createCriteria(Glfy.class)
                .createAlias("sydw", "sydw", CriteriaSpecification.LEFT_JOIN)
                .createAlias("fylb", "fylb", CriteriaSpecification.LEFT_JOIN)
                .createAlias("wljsd", "jsd", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("sydw.id").as("sydw.id"))
                                .add(Property.forName("sydw.qc").as("sydw.qc"))
                                .add(Property.forName("fylb.id").as("fylb.id"))
                                .add(Property.forName("fylb.fykmjc").as("fylb.fykmjc"))
                                .add(Property.forName("jfsl").as("jfsl"))
                                .add(Property.forName("dj").as("dj"))
                                .add(Property.forName("je").as("je"))
                                .add(Property.forName("jsd.jsdh").as("jsdh"))
                                .add(Property.forName("bz").as("bz"))
                ).add(Restrictions.eq("cgd.id", id))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        return (List<Map>) criteria.list();
    }

    public List<Map> findCgdmxSearch(Long cgjsdId) {
        Criteria criteria = getSession().createCriteria(Cgdmx.class)
                .createAlias("gg", "gg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgjsd", "cgjsd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgd", "cgd", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("id").as("id"))
                                .add(Property.forName("cgd.id").as("cgd.id"))
                                .add(Property.forName("cgd.cgdh").as("cgdh"))
                                .add(Property.forName("gg.id").as("wzgg.id"))
                                .add(Property.forName("gg.code").as("wzgg.code"))
                                .add(Property.forName("gg.name").as("wzgg.name"))
                                .add(Projections.sqlProjection("gg1_.ggxh as \"wzgg.ggxh\"", new String[]{"wzgg.ggxh"}, new Type[]{Hibernate.STRING}))
                                .add(Property.forName("gg.dw").as("wzgg.dw"))
                                .add(Property.forName("ck").as("ck"))
                                .add(Property.forName("cd.id").as("cd.id"))
                                .add(Property.forName("rksl").as("rksl"))
                                .add(Property.forName("sbsl").as("sbsl"))
                                .add(Property.forName("jzfs.id").as("jzfs.id"))
                                .add(Property.forName("jzfs.z").as("jzfs.z"))
                                .add(Property.forName("hsj").as("hsj"))
                                .add(Property.forName("sjdj").as("sjdj"))
                                .add(Property.forName("bhsj").as("bhsj"))
                                .add(Property.forName("hse").as("hse"))
                                .add(Property.forName("bhse").as("bhse"))
                                .add(Property.forName("jsbhse").as("je"))
                                .add(Property.forName("jsse").as("se"))
                                .add(Property.forName("jsce").as("ce"))
                                .add(Property.forName("cgjsd.me").as("me"))
                                .add(Property.forName("cgjsd.ddfy").as("ddfy"))
                ).add(Restrictions.eq("cgjsd.id", cgjsdId))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        criteria.addOrder(Property.forName("gg.code").asc());
        return criteria.list();
    }

    public List<Map> findGysByCgd(Long gsjgId, Long sbgsId, String gysJc) {
        String hql = "select distinct gys.id as id,gys.jc as jc, qyfr.nsh as qyfr_nsh from Cgd cgd left join cgd.gys gys left join gys.qyfr qyfr where cgd.yjs=false and cgd.cggs.id=? and cgd.sbgs.id=?";
        if (StringUtils.isNotEmpty(gysJc)) {
            hql += " and gys.jc like ?";
        }
        Query query = this.getSession().createQuery(hql);
        query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        query.setLong(0, gsjgId);
        query.setLong(1, sbgsId);
        if (StringUtils.isNotEmpty(gysJc)) {
            query.setString(2, gysJc + "%");
        }
        return query.list();
    }

    /**
     * 首页显示采购单数
     *
     * @param
     * @param yrk
     * @return
     */
    public BigDecimal findCgdList(String sfys, String yrk) {
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String regionSql = StringUtils.EMPTY;
        if (existsSQLQuery("select 1 from t_sys_yg_bmfw where yg_id = ?", YgService.getCurrentYg().getId())) {
            regionSql += "  inner join v_wzbm_gg_yg_search bmfw  on cgdmx6_.GG_ID = bmfw.ggid and bmfw.yg_id=" + YgService.getCurrentYg().getId();
        } else if (existsSQLQuery("select 1 from v_wzbm_gg_search  where gsid = ?", YgService.getCurrentYgGsId())) {
            regionSql += "  inner join v_wzbm_gg_search bmfw on cgdmx6_.GG_ID = bmfw.ggid and bmfw.gsid=" + YgService.getCurrentYgGsId();
        }
        //==============记录数量===============
        String sqlCountBase = "select count(distinct cgd0_.id) from T_BIZ_CGD cgd0_ left outer join T_BIZ_HT cght1_ on cgd0_.CGHT_ID = cght1_.id " +
                " left outer join T_BIZ_CGDMX cgdmx6_ on cgd0_.ID = cgdmx6_.CGD_ID and cgdmx6_.deleted!=1 " + regionSql;

        String whereSql = " where (cgd0_.deleted != '1') and cgd0_.zt <> 'ybc' and (cgd0_.cglx <> 'zddh' or cgd0_.cglx = 'zddh' and cght1_.rzlx = '2')";

        if (sfys != null && !sfys.isEmpty()) {
            condition.add(" cgd0_.sfys = :sfys");
            param.put("sfys", "true".equals(sfys));
        }
        if (yrk != null && !yrk.isEmpty()) {
            condition.add(" cgd0_.yrk = :yrk");
            param.put("yrk", "true".equals(yrk));
        }
        Date end = DaysAfterNowDate(new Date());
        condition.add(" cgd0_.jhdhrq>= :end ");
        param.put("end", end);

        String dataRegion = createSQLDataRegion(null, "cgd0_");
        if (!condition.isEmpty())
            whereSql += " and " + StringUtils.join(condition, " and ");

        String countSql = sqlCountBase + whereSql + " and " + dataRegion;


        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setProperties(param).uniqueResult();

        return count;
    }

    public List<Map> findCgmxByKh(Kh kh) {
        Criteria criteria = getSession().createCriteria(Kh.class)
                .createAlias("cgdmx", "cgdmx", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jtwz", "gg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgdmx.jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgd", "cgd", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("cgdmx.id").as("cgdmx.id"))
                                .add(Property.forName("gg.id").as("wzgg.id"))
                                .add(Property.forName("gg.code").as("wzgg.code"))
                                .add(Property.forName("gg.name").as("wzgg.name"))
                                .add(Projections.sqlProjection("gg2_.cz||' '||gg2_.ggxh as \"wzgg.ggxh\"", new String[]{"wzgg.ggxh"}, new Type[]{Hibernate.STRING}))
                                .add(Property.forName("gg.dw").as("wzgg.dw"))
                                .add(Property.forName("cd.id").as("cd.id"))
                                .add(Property.forName("cd.z").as("cd.z"))
                                .add(Property.forName("cgdmx.ck").as("ck"))
                                .add(Property.forName("cgdmx.js").as("js"))
                                .add(Property.forName("cgdmx.sbsl").as("sbsl"))
                                .add(Property.forName("cgdmx.rkjs").as("rkjs"))
                                .add(Property.forName("cgdmx.rkjz").as("rkjz"))
                                .add(Property.forName("cgdmx.rksl").as("rksl"))
                                .add(Property.forName("khbm").as("khbm"))
                                .add(Property.forName("cgd.cgdh").as("cgd.cgdh"))
                                .add(Property.forName("jzfs.id").as("jzfs.id"))
                                .add(Property.forName("jzfs.z").as("jzfs.z"))
                                .add(Property.forName("cgdmx.hsj").as("hsj"))
                                .add(Property.forName("cgdmx.bhsj").as("bhsj"))
                                .add(Property.forName("cgdmx.bz").as("bz"))
                ).add(Restrictions.eq("id", kh.getId()))
                .add(Restrictions.isNotNull("cgd.id"))
                .add(Restrictions.eq("cgd.deleted", 0))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        criteria.addOrder(Property.forName("gg.code").asc());
        return (List<Map>) criteria.list();
    }


    public Double calcCgdmxSbsl(Long hzmxid, Cgdmx cgdmx) {
        String sql = "select sum(sbsl) as \"sum\" from T_BIZ_CGDMX mx,T_BIZ_JHCGDDZ dz where mx.id=dz.cgdmxid and dz.hzmxid=:hzmxid";
        Map<String, Object> param = new HashMap<String, Object>();
        param.put("hzmxid", hzmxid);
        List<Map> list = createSQLQuery(sql)
                .setProperties(param)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();
        if (list != null) {
            Object value = ((Map) list.get(0)).get("sum");
            if (value == null)
                return cgdmx.getSbsl();
            else
                return ((BigDecimal) value).doubleValue() + cgdmx.getSbsl();
        } else
            return cgdmx.getSbsl();
    }

    public List<Map> findBgcgdSyncList() {
        String sql = "select * from t_biz_cgd_wg where flag = 1 order by zt,date_created";
        return (List<Map>) createSQLQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
    }

    public Object[] findBgcgdSyncDetail(Long cgdId) {
        String sql = "select cgd.cgdh as stbh, cgd.cgdh as shdh, gys.bggysbm as gysbh," +
                "nvl(jhdw.jhdwqc,' ') as shdwmc,decode(cgd.yrk,0,2,1,1,2) as djzt," +
                "cgd.date_created as zdrq, cgd.jhdhrq as xsdrq,cgd.rksj as shrq," +
                "nvl(y1.xm,' ') as zdr, nvl(y2.xm,' ') as shr,nvl(y3.xm,' ') as ysy," +
                "to_char(cgd.date_created,'yyyy') as nian, to_char(cgd.date_created,'MM') as yue," +
                "nvl(cgd.sftzd,0) as tzflag,cgd.bz as bz," +
                "(case when hzcgd.sbcl='sb' then '2' else '1' end) as djlx, 1 as gsdm,cgd.id+200000 as wlid," +
                "' ' as  dcclbz from t_biz_cgd cgd left join t_sys_yg y1 on cgd.createuser_id = y1.id " +
                "left join t_sys_yg y2 on cgd.rkr_id = y2.id left join t_sys_yg y3 on cgd.ysr_id = y3.id " +
                "left join t_sys_bggys gys on cgd.gys_id=gys.qyjg_id " +
                "left join ( select distinct mx.sbcl,cgdmx.cgd_id,gs.qc " +
                "from t_biz_bgjhhzmx mx,t_biz_bgjhhz hz,t_biz_jhcgddz dz,t_biz_cgdmx cgdmx,t_sys_gsjg gs " +
                "where mx.deleted=0 and hz.deleted=0 and mx.bgjhhz_id=hz.id  " +
                "and dz.hzmxid=mx.id and dz.cgdmxid=cgdmx.id and hz.jhdw_id=gs.id " +
                ") hzcgd on hzcgd.cgd_id=cgd.id " +
                " left join (select jhdw.qc jhdwqc,ck.id ckid from t_sys_gsjg gyz, t_sys_ck ck, t_sys_lj lj,t_lj_xy xy,t_sys_gsjg jhdw " +
                " where ck.SSGYZ_ID = gyz.id and gyz.id = lj.gyz_id and lj.deleted = 0 and ck.deleted = 0 and gyz.deleted = 0 " +
                "   and xy.lj_id = lj.id and jhdw.deleted = 0 and xy.xy_id = jhdw.id) jhdw on jhdw.ckid = cgd.dzck_id " +
                " where cgd.cglx='bght' and cgd.id = ?";
        return (Object[]) createSQLQuery(sql).setParameter(0, cgdId).uniqueResult();
    }

    public Object[] findWjswz(Long cgdId) {
        String sql = "select cgd.cgdh stbh, jhdw.jhdwqc as shdwmc,ywks.ksbh ksbh,cgd.cgdh shdh,cgd.rksj dhrq,gys.bggysbm gysbh, " +
                " nvl(cgd.yjs,0) jsbz,nvl(cgd.sftzd,0) tzflag,1 gsdm,(case when hzcgd.sbcl='sb' then '2' else '1' end) as lb, " +
                "       cgd.id+200000 as wlid  from t_biz_cgd cgd " +
                "  left join (select jhdw.qc jhdwqc, ck.id ckid from t_sys_gsjg gyz,t_sys_ck ck,t_sys_lj lj,t_lj_xy xy,t_sys_gsjg jhdw " +
                "  where ck.SSGYZ_ID = gyz.id and gyz.id = lj.gyz_id and lj.deleted = 0 and ck.deleted = 0 and gyz.deleted = 0 " +
                "                and xy.lj_id = lj.id and jhdw.deleted = 0 and xy.xy_id = jhdw.id) jhdw on jhdw.ckid = cgd.dzck_id " +
                "  left join t_sys_bgywks ywks on ywks.ywks_id = cgd.sbbm_id  " +
                "  left join t_sys_bggys gys on cgd.gys_id=gys.qyjg_id  " +
                "  left join ( select distinct mx.sbcl,cgdmx.cgd_id,gs.qc  " +
                "  from t_biz_bgjhhzmx mx,t_biz_bgjhhz hz,t_biz_jhcgddz dz,t_biz_cgdmx cgdmx,t_sys_gsjg gs  " +
                "  where mx.deleted=0 and hz.deleted=0 and mx.bgjhhz_id=hz.id  " +
                "  and dz.hzmxid=mx.id and dz.cgdmxid=cgdmx.id and hz.jhdw_id=gs.id ) hzcgd on hzcgd.cgd_id=cgd.id " +
                "  where cgd.cglx='bght' and cgd.id = ?";
        return (Object[]) createSQLQuery(sql).setParameter(0, cgdId).uniqueResult();
    }

    public List<Object[]> findBgcgdSyncMxList(Long cgdId) {
        String sql = "select cgd.cgdh as shdh,nvl(ht.htbm,' ') as htbh,g.oldcode as wzbh, " +
                "g.name as wzmc,nvl(mx.sbsl,0) as shsl,mx.rksl as sssl," +
                "mx.rksl as sumsssl,mx.hsj as shdj,mx.bhsj as notaxdj, " +
                "g.ggxh as xhgg,g.th as th,g.cz as cz,nvl(g.dw,'.') as jldw, " +
                "cd.z as phsb,mx.id+200000 as wlid " +
                "from t_biz_cgdmx mx left join t_biz_cgd cgd on mx.cgd_id = cgd.id " +
                "left join t_biz_ht ht on ht.id = cgd.cght_id left join t_sys_gg g on g.id = mx.gg_id " +
                "left join t_sys_sjzdz cd on cd.id = mx.sjzdz_cd_id " +
                "where cgd.cglx='bght' and cgd.id = ?  and cgd.deleted=0";
        return (List<Object[]>) createSQLQuery(sql).setParameter(0, cgdId).list();
    }

    public List<Object[]> findWjswzmx(Long cgdId) {
        String sql = " select cgd.cgdh as stdh,g.oldcode as wzbh,nvl(ht.htbm,' ') as htbh, " +
                "   mx.rksl as sssl,mx.hsj as shdj,mx.bhsj as notaxdj,  " +
                "   g.name as wzmc,g.ggxh as xhgg,g.th as th,g.cz as cz,nvl(g.dw,'.') as jldw,  " +
                "   cd.z as phsb,mx.id + 200000 as wlid  " +
                "   from t_biz_cgdmx mx left join t_biz_cgd cgd on mx.cgd_id = cgd.id " +
                "   left join t_biz_ht ht on ht.id = cgd.cght_id left join t_sys_gg g on g.id = mx.gg_id " +
                "   left join t_sys_sjzdz cd on cd.id = mx.sjzdz_cd_id " +
                "   where cgd.cglx='bght' and cgd.id = ?  and cgd.deleted=0";
        return (List<Object[]>) createSQLQuery(sql).setParameter(0, cgdId).list();
    }

    //入库状态主表更新
    public Object[] findBgcgdSyncDhxx(Long cgdId) {
        String sql = "select 1 as djzt, nvl(yb.xm,' ') as shy,nvl(ya.xm,' ') as ysy,cgd.rksj as shrq from t_biz_cgd cgd left join t_sys_yg ya on ya.id = cgd.ysr_id left join t_sys_yg yb on yb.id = cgd.rkr_id where cgd.id = ?";
        return (Object[]) createSQLQuery(sql).setParameter(0, cgdId).uniqueResult();
    }

    public List<Object[]> findBgcgdSyncDhmx(Long cgdId) {
        String sql = "select nvl(mx.sbsl,0) as shsl,mx.rksl as sssl,mx.rksl as sumsssl,mx.id+200000 as id " +
                "from t_biz_cgdmx mx left join t_biz_cgd cgd on cgd.id = mx.cgd_id  " +
                "where cgd.cglx='bght' and cgd.id = ?  and cgd.deleted=0";
        return (List<Object[]>) createSQLQuery(sql).setParameter(0, cgdId).list();
    }

    public String findBgcgdSyncMxIds(Long cgdId) {
        String sql = "select  wm_concat(id+200000) from t_biz_cgdmx where cgd_id = ?";
        return (String) createSQLQuery(sql).setParameter(0, cgdId).uniqueResult();
    }

    public Long findCgdByCgdmxId(Long cgdmxId) {
        String sql = " select cgd_id from t_biz_cgdmx where id = ?";
        return ((BigDecimal) createSQLQuery(sql).setParameter(0, cgdmxId).uniqueResult()).longValue();
    }

    public Boolean checkSerialNumber(String serialNumber) {
        String sql = "select count(1) from t_biz_cgd where serialNumber = ?";
        BigDecimal count = (BigDecimal) createSQLQuery(sql).setParameter(0, serialNumber.trim()).uniqueResult();
        if (count.doubleValue() > 0d) {
            return false;
        } else {
            return true;
        }
    }

    public List<Map> findrkmxList(Long id) {
        String sql = new String();
        sql = "select distinct g.id as  \"wzgg.id\"," +
                "       g.oldcode as \"wzgg.oldcode\"," +
                "       g.code as \"wzgg.code\"," +
                "       g.name as \"wzgg.name\"," +
                "       g.ggxh as \"wzgg.ggxh\"," +
                "       g.th as \"wzgg.th\"," +
                "       (select dlkh.qc from t_sys_gsjg dlkh where dlkh.id = jh.jhdw_id and dlkh.deleted != 1) as \"dlkh.qc\"," +
                "       dz.id as \"cd.id\"," +
                "       dz.z as \"cd.z\"," +
                "       jzfs.id as  \"jzfs.id\" ," +
                "       jzfs.z as  \"jzfs.z\" ," +
                "       jhmx.jhjs as \"js\"," +
                "       jhmx.jhsl as \"sbsl\"," +
                "       jhmx.jhjs as \"js\"," +
                "       mx.hsj as \"hsj\"," +
                "       mx.bhsj as \"bhsj\"," +
                "       mx.rkjs as \"rkjs\"," +
//                "       mx.sbsl as \"sbsl\"," +
                "       mx.sssl as \"sssl\"," +
                "       mx.ssjs as \"ssjs\"," +
                "       mx.sku as \"sku\"," +
                "       mx.rkjz as \"rkjz\"," +
                "       mx.rksl as \"rksl\"," +
                "       mx.id as \"mx.id\"," +
                "       mx.mxh as \"mxh\"," +
                "       kh.id as \"kh.id\"," +
                "       kh.khbm as \"kh.khbm\", " +
                "       nvl((select sum(zkh.js) from t_biz_kh zkh left join t_sys_gsjg jhdw on zkh.sbgs_id = jhdw.id left join t_biz_cgdmx mx1 on zkh.cgdmx_id = mx1.id where mx1.id = mx.id and jhdw.id = jh.jhdw_id and mx1.deleted = 0 and zkh.qk = 0),0) as \"yrjs\"," +
                "       nvl((select sum(zkh.sl) from t_biz_kh zkh left join t_sys_gsjg jhdw on zkh.sbgs_id = jhdw.id left join t_biz_cgdmx mx1 on zkh.cgdmx_id = mx1.id where mx1.id = mx.id and jhdw.id = jh.jhdw_id and mx1.deleted = 0 and zkh.qk = 0),0) as \"yrsl\", " +
                "       jhmx.id as \"jhmx.id\"" +
                "  from t_biz_cgdmx mx\n" +
                "  inner join t_biz_cgd c on mx.cgd_id = c.id and c.deleted!=1" +
                "  left join t_sys_sjzdz dz on dz.id = mx.sjzdz_cd_id and dz.deleted!=1" +
                "  left join t_sys_sjzdz jzfs on mx.szjdz_jzfs_id = jzfs.id and jzfs.deleted!=1" +
                "  inner join t_sys_gg g on mx.gg_id = g.id " +
                "  inner join t_biz_ht ht on c.cght_id = ht.id and ht.deleted!=1" +
                "  inner join t_biz_bgjhhz hz on ht.bgjhhz_id = hz.id and hz.deleted!=1" +
                "  inner join t_biz_bgjh jh on jh.bgjhhz_id = hz.id and jh.deleted!=1" +
                "  inner join t_biz_bgjhmx jhmx on  jh.id = jhmx.bgjh_id and mx.gg_id=jhmx.wzgg_id and jhmx.deleted!=1 and mx.sjzdz_cd_id=jhmx.sjzdz_cd_id" +
                "  left join t_biz_kh kh on kh.id=mx.kh_id where c.id = ? order by dz.id";

        List<Map> list = createSQLQuery(sql, id).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return list;
    }

    public List<Map> findrkmxDetailList(Long id) {
        String sql = new String();
        sql = "select  g.id      as \"wzgg.id\",\n" +
                "       g.oldcode as \"wzgg.oldcode\",\n" +
                "       g.code    as \"wzgg.code\",\n" +
                "       g.name    as \"wzgg.name\",\n" +
                "       g.ggxh    as \"wzgg.ggxh\",\n" +
                "       g.th      as \"wzgg.th\",\n" +
                "       dz.id     as \"cd.id\",\n" +
                "       dz.z      as \"cd.z\",\n" +
                "       jzfs.id   as \"jzfs.id\",\n" +
                "       jzfs.z    as \"jzfs.z\",\n" +
                "       kh.jhsl as \"sbsl\",\n" +
                "       kh.jhjs     as \"js\",\n" +
                "       mx.hsj    as \"hsj\",\n" +
                "       mx.bhsj   as \"bhsj\",\n" +
                "       kh.cshjs  as \"rkjs\",\n" +
                "       mx.rkjz   as \"rkjz\",\n" +
                "       kh.cshsl   as \"rksl\",\n" +
                "       mx.id     as \"mx.id\",\n" +
                "       kh.id     as \"kh.id\",\n" +
                "       kh.khbm   as \"kh.khbm\"\n" +
                "  from t_biz_kh kh " +
                "  left join t_biz_cgd c " +
                "    on kh.cgd_id = c.id and c.deleted!=1" +
                "  left join t_biz_cgdmx mx " +
                "    on kh.cgdmx_id = mx.id and mx.deleted!=1" +
                "  left join t_sys_gg g " +
                "    on kh.jtwz_id = g.id " +
                "  left join t_sys_sjzdz dz " +
                "    on kh.cd_sjzdz_id = dz.id and dz.deleted!=1" +
                "  left join t_sys_sjzdz jzfs " +
                "    on mx.szjdz_jzfs_id = jzfs.id and jzfs.deleted!=1" +
                " where c.id = ?";
        List<Map> list = createSQLQuery(sql, id).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return list;
    }


    public Cgd findCgdFindCgdh(String cgdh) {
        String hql = "select cgd from Cgd cgd where cgd.cgdh = ?";
        return (Cgd) findUnique(hql, cgdh.trim());
    }


    public void gbjkUpdate(Long id) {
        String sql = "update t_biz_cgdmx mx\n" +
                "   set mx.ssjs = mx.js,\n" +
                "       mx.sssl = mx.sbsl,\n" +
                "       mx.rkjs = mx.js,\n" +
                "       mx.rksl = mx.sbsl\n" +
                " where cgd_id in(select c.id from t_biz_cgd c where c.id =?)";
        createSQLQuery(sql, id).executeUpdate();
    }


    public void jsCgdUpdate(Long cghtId) {
        String sql=" update t_biz_cgd  cgd\n" +
                "                  set " +
                "             cgd.zt= 'ysp',\n" +
                "             cgd.yrk=1 \n" +
                "                 where cgd.cght_id=?";


        createSQLQuery(sql, cghtId).executeUpdate();
    }


    public void gbjkUpdate2(Long id) {
        String sql = " update t_biz_cgd  cgd\n" +
                "   set cgd.zt= 'yfk',\n" +
                "       cgd.ysh= 0,\n" +
                "       cgd.yrk= 0,\n" +
                "       cgd.endflag =1\n" +
                "  where cgd.id=?";
        this.executeSQL(sql, id);
        // createSQLQuery(sql, id).executeUpdate();
    }

    public Double getSfbzjJe(Double bzjBl, List<Sfk> skList, Cght cght) {
        // 总余额
        Double zye = 0.0;
        for (Sfk sfk : skList) {
            if (sfk.getFkfs().getId() == 9) {
                Connection con = null;
                CallableStatement call = null;
                try {
                    con = getSession().connection();
                    System.out.print("r_biz_kyye...");
                    call = con.prepareCall("{Call R_BIZ_KYYE(?,?,?)}");
                    call.setLong(1, cght.getMxts());
                    call.setDouble(2, cght.getTxl());
                    call.setLong(3, sfk.getId());
                    call.registerOutParameter(4, Types.VARCHAR);
                    call.execute();
                    Double kyye = call.getDouble(4);
                    zye += kyye;
                } catch (Exception e) {
                    e.printStackTrace();
                    throw new RuntimeException(e.getMessage());
                } finally {
                    closeResource(null, call, con);
                }
            } else {
                zye += sfk.getYe();
            }
        }

        return zye * bzjBl / 100;
    }

    private void closeResource(ResultSet rs, Statement s, Connection con) {
        if (rs != null)
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (s != null)
            try {
                s.close();
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (con != null)
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
    }


    @Autowired
    public void setCgdmxDao(CgdmxDao cgdmxDao) {
        this.cgdmxDao = cgdmxDao;
    }

    @Autowired
    public void setYgDao(YgDao ygDao) {
        this.ygDao = ygDao;
    }


}
